package net.lastcoder.ifserver.util;

import net.lastcoder.ifserver.Constants;
import net.lastcoder.ifserver.database.DbConnectionManager;
import net.lastcoder.ifserver.database.DBHelper;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * auth: WenYF
 * date: 2017/8/4
 */
public class DBUtil {
    protected static final Logger Log = LoggerFactory.getLogger(DBUtil.class);

    public static int delete(String table, String[] keysWithEqual, Object[] values, String dbname) {
        if (keysWithEqual.length != values.length) {
            Log.debug("delete: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keysWithEqual.length + ", values:" + values.length);
            return 0;
        }

        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keysWithEqual, " AND ");
            String sql = "DELETE FROM " + table + " WHERE " + concatKeys;
            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keysWithEqual.length; i++) {
                pst.setObject(i, values[i - 1]);
            }

            return pst.executeUpdate();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(pst, con);
        }
    }

    /**如果存在则返回查询的值，不存在返回null
     * @param table
     * @param projects
     * @param keysWithEqual
     * @param values
     * @return 如果没有找到 返回NULL
     */
    public static Object[] queryOne(String table, String[] projects, String[] keysWithEqual
            , Object[] values, String dbname) {
        if (keysWithEqual.length != values.length) {
            Log.debug("query: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keysWithEqual.length + ", values:" + values.length);
            return null;
        }
        long t1 = System.currentTimeMillis();

        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keysWithEqual, " AND ");
            String concatProjects = TextUtils.concatStringNotEnd(projects, ",");
            String sql = "SELECT " + concatProjects + " FROM " + table + " WHERE " + concatKeys
                    + " LIMIT 1";

            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keysWithEqual.length; i++) {
                pst.setObject(i, values[i - 1]);
            }

            rs = pst.executeQuery();
            Object[] rets = null;
            if (rs.next()) {
                rets = new Object[projects.length];
                for (int i = 0; i < rets.length; i++) {
                    rets[i] = rs.getObject(i + 1);
                }
            }

            return rets;
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return null;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);
            if (Constants.isDebug()) {
                Log.info(table + " queryOne time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**
     * @param table
     * @param projects
     * @param keysWithEqual
     * @param values
     * @param order
     * @return 如果没有找到 返回NULL
     */
    public static Object[] queryOne(String table, String[] projects, String[] keysWithEqual
            , Object[] values, String order, String dbname) {
        if (keysWithEqual.length != values.length) {
            Log.debug("query: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keysWithEqual.length + ", values:" + values.length);
            return null;
        }
        long t1 = System.currentTimeMillis();

        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keysWithEqual, " AND ");
            String concatProjects = TextUtils.concatStringNotEnd(projects, ",");
            String sql = "SELECT " + concatProjects + " FROM " + table + " WHERE " + concatKeys
                   + " ORDER BY " + order + " LIMIT 1";

            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keysWithEqual.length; i++) {
                pst.setObject(i, values[i - 1]);
            }

            rs = pst.executeQuery();

            Object[] rets = null;
            if (rs.next()) {
                rets = new Object[projects.length];
                for (int i = 0; i < rets.length; i++) {
                    rets[i] = rs.getObject(i + 1);
                }
            }

            return rets;
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return null;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);
            if (Constants.isDebug()) {
                Log.info(table + " queryOne time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**
     * @param table
     * @param projects
     * @param keysWithEqual
     * @param values
     * @param order
     * @param dbname
     * @return 如果没有找到 返回一个长度为0的集合
     */
    public static Object[] query(String table, String[] projects, String[] keysWithEqual
            , Object[] values, String order, String dbname) {
        if (keysWithEqual.length != values.length) {
            Log.debug("query: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keysWithEqual.length + ", values:" + values.length);
            return null;
        }
        long t1 = System.currentTimeMillis();

        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keysWithEqual, " AND ");
            String concatProjects = TextUtils.concatStringNotEnd(projects, ",");
            String sql = "SELECT " + concatProjects + " FROM " + table + " WHERE " + concatKeys
                    + " ORDER BY " + order;

            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keysWithEqual.length; i++) {
                pst.setObject(i, values[i - 1]);
            }

            rs = pst.executeQuery();

            List<Object> lists = new ArrayList<>();
            int index = 0;
            while (rs.next()) {
                for (int i = 0; i < projects.length; i++) {
                    lists.add(index++, rs.getObject(i + 1));
                }
            }

            return lists.toArray();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return null;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);
            if (Constants.isDebug()) {
                Log.info(table + " query time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**
     * @param table
     * @param projects
     * @param keysWithEqual
     * @param values
     * @param order
     * @param limit
     * @param dbname
     * @return 如果没有找到 返回一个长度为0的集合
     */
    public static Object[] query(String table, String[] projects, String[] keysWithEqual
            , Object[] values, String order, int limit, String dbname) {
        if (keysWithEqual.length != values.length) {
            Log.debug("query: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keysWithEqual.length + ", values:" + values.length);
            return null;
        }
        long t1 = System.currentTimeMillis();

        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keysWithEqual, " AND ");
            String concatProjects = TextUtils.concatStringNotEnd(projects, ",");
            String sql = "SELECT " + concatProjects + " FROM " + table + " WHERE " + concatKeys
                    + " ORDER BY " + order + " LIMIT " + limit;

            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keysWithEqual.length; i++) {
                pst.setObject(i, values[i - 1]);
            }

            rs = pst.executeQuery();

            List<Object> lists = new ArrayList<>();
            int index = 0;
            while (rs.next()) {
                for (int i = 0; i < projects.length; i++) {
                    lists.add(index++, rs.getObject(i + 1));
                }
            }

            return lists.toArray();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return null;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);

            if (Constants.isDebug()) {
                Log.info(table + " query time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    public static boolean insert(String table, String[] keys, Object[] values, String dbname) {
        if (keys.length != values.length) {
            Log.debug("insert: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keys.length + ", values:" + values.length);
            return false;
        }
        long t1 = System.currentTimeMillis();

        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keys, ",");
            String quest = TextUtils.concatStringNotEnd("?", values.length, ",");
            String sql = "INSERT INTO " + table + " (" + concatKeys + ") VALUES (" + quest + ")";

            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keys.length; i++) {
                pst.setObject(i, values[i-1]);
            }

            return pst.executeUpdate() > 0;
        } catch (SQLException e) {
            Log.error(dbname + " : " + e.getMessage(), e);
            return false;
        } finally {
            DbConnectionManager.closeConnection(pst, con);

            if (Constants.isDebug()) {
                Log.info(table + " insert time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**
     * @param table
     * @param keysWithEqual 带符号的，如abc=? 或 abcd>?
     * @param values
     * @return
     */
    public static boolean isExist(String table, String[] keysWithEqual, Object[] values, String dbname) {
        if (keysWithEqual.length != values.length) {
            Log.debug("queryRows: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keysWithEqual.length + ", values:" + values.length);
            return false;
        }
        long t1 = System.currentTimeMillis();

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(keysWithEqual, " AND ");
            String sql = "SELECT 1 FROM " + table + " WHERE " + concatKeys + " LIMIT 1";

            pst = con.prepareStatement(sql);

            for (int i = 1; i <= keysWithEqual.length; i++) {
                pst.setObject(i, values[i-1]);
            }

            rs = pst.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return false;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);

            if (Constants.isDebug()) {
                Log.info(table + " isExist time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**更新
     * @param table 表
     * @param conditionKeysWithEqual 带有符号的key，如“c1<?”
     * @param conditionValues 条件值
     * @param keys 改变的值
     * @param values 改变的value
     * @return rows
     */
    public static int update(String table, String[] conditionKeysWithEqual, Object[] conditionValues
            , String[] keys, Object[] values, String dbname) {
        if (keys.length != values.length) {
            Log.debug("update: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keys.length + ", values:" + values.length);
            return 0;
        }
        long t1 = System.currentTimeMillis();

        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(conditionKeysWithEqual, " AND ");
            String valueKeys = TextUtils.concatStringNotEnd(keys, "=?", ",");
            String sql = "UPDATE " + table + " SET " + valueKeys + " WHERE " + concatKeys;

            pst = con.prepareStatement(sql);
            int i = 1;
            for (; i <= values.length; i++) {
                pst.setObject(i, values[i-1]);
            }

            for (; i <= values.length + conditionValues.length; i++) {
                pst.setObject(i, conditionValues[i - values.length - 1]);
            }

            return pst.executeUpdate();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(pst, con);

            if (Constants.isDebug()) {
                Log.info(table + " update time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**更新
     * @param table 表
     * @param conditionKeysWithEqual 带有符号的key，如“c1<?”
     * @param conditionValues 条件值
     * @param keys 改变的值
     * @param values 改变的value
     * @return rows
     */
    public static int updateOne(String table, String[] conditionKeysWithEqual, Object[] conditionValues
            , String[] keys, Object[] values, String dbname) {
        if (keys.length != values.length) {
            Log.debug("update: 的时候keys的长度和values的长度不一致, " +
                    "keys:" + keys.length + ", values:" + values.length);
            return 0;
        }

        long t1 = System.currentTimeMillis();
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = DBHelper.getConnection(dbname);
            String concatKeys = TextUtils.concatStringNotEnd(conditionKeysWithEqual, " AND ");
            String valueKeys = TextUtils.concatStringNotEnd(keys, "=?", ",");
            String sql = "UPDATE " + table + " SET " + valueKeys + " WHERE " + concatKeys + " LIMIT 1";

            pst = con.prepareStatement(sql);
            int i = 1;
            for (; i <= values.length; i++) {
                pst.setObject(i, values[i-1]);
            }

            for (; i <= values.length + conditionValues.length; i++) {
                pst.setObject(i, conditionValues[i - values.length - 1]);
            }

            return pst.executeUpdate();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(pst, con);

            if (Constants.isDebug()) {
                Log.info(table + " updateOne time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**
     * @param sql
     * @param resultLength
     * @param dbname
     * @return  如果没有找到 返回NULL
     */
    public static Object[] queryOneRaw(String sql, int resultLength, String dbname) {
        long t1 = System.currentTimeMillis();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = DBHelper.getConnection(dbname);

            if (!sql.contains("limit") && !sql.contains("Limit") && !sql.contains("LIMIT")) {
                sql = sql + " limit 1";
            }

            pst = con.prepareStatement(sql);

            rs = pst.executeQuery();

            Object[] rets = null;
            if (rs.next()) {
                rets = new Object[resultLength];
                for (int i = 0; i < resultLength; i++) {
                    rets[i] = rs.getObject(i + 1);
                }
            }

            return rets;
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return null;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);

            if (Constants.isDebug()) {
                Log.info(sql + " queryOneRaw time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**
     * @param sql sql语句
     * @param projectSize 每一行要查询的结果数
     * @param dbname 数据库
     * @return  如果没有找到 返回NULL
     */
    public static List<Object> queryRaw(String sql, int projectSize, String dbname) {
        long t1 = System.currentTimeMillis();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = DBHelper.getConnection(dbname);

            pst = con.prepareStatement(sql);

            rs = pst.executeQuery();

            List<Object> rets = new ArrayList<>();
            while (rs.next()) {
                for (int i = 0; i < projectSize; i++) {
                    rets.add(rs.getObject(i + 1));
                }
            }

            return rets;
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return null;
        } finally {
            DbConnectionManager.closeConnection(rs, pst, con);

            if (Constants.isDebug()) {
                Log.info(sql + " queryOneRaw time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

    /**更新
     * @param rawSql sql
     * @param dbname dbname
     * @return rows
     */
    public static int updateRaw(String rawSql, String dbname) {

        long t1 = System.currentTimeMillis();
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = DBHelper.getConnection(dbname);
            pst = con.prepareStatement(rawSql);

            return pst.executeUpdate();
        } catch (SQLException e) {
            Log.error(e.getMessage(), e);
            return 0;
        } finally {
            DbConnectionManager.closeConnection(pst, con);

            if (Constants.isDebug()) {
                Log.info(rawSql + " updateOne time : " + (System.currentTimeMillis() - t1));
            }
        }
    }

//    /**更新
//     * @param table 表
//     * @param condition 条件  where
//     * @param keys 改变的值
//     * @param values 改变的value
//     * @return rows
//     */
//    public static int update(String table, String condition, String[] keys, Object[] values) {
//        if (keys.length != values.length) {
//            Log.debug("update: 的时候keys的长度和values的长度不一致, " +
//                    "keys:" + keys.length + ", values:" + values.length);
//            return 0;
//        }
//
//        Connection con = null;
//        PreparedStatement pst = null;
//        try {
//            con = DbConnectionManager.getConnection();
//            String keyValues = TextUtils.concatStringNotEnd(keys, "=?", ",");
//            String sql = "UPDATE " + table + " SET " + keyValues + " WHERE " + condition;
//
//            pst = con.prepareStatement(sql);
//
//            for (int i = 1; i <= keys.length; i++) {
//                pst.setObject(i, values[i-1]);
//            }
//
//            return pst.executeUpdate();
//        } catch (SQLException e) {
//            Log.error(e.getMessage(), e);
//            return 0;
//        } finally {
//            DbConnectionManager.closeConnection(pst, con);
//        }
//    }
}
